    package server;

import game.resources.History;
import game.resources.Profile;
import game.resources.ReverseIntegerComparator;
import game.resources.History.historyEntry;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.commons.lang.StringEscapeUtils;



public class DBLogic {
	
	DBAccess DBA = new DBAccess();
	
	public static final String DELIMITER = ";;";
	
	
	public boolean InitDBConnection() {
		return DBA.InitDBConnection();
	}
	
	public void ShutDBConnection() {
		DBA.ShutDBConnection();
	}
	
	public boolean addNewProfile(Profile profile) {
		int check = DBA.WriteToDB("INSERT INTO USERS (USERID, FIRSTNAME, LASTNAME, RATING) " +
			 "VALUES ('"+profile.getUserID()+"', '"+profile.getFirstName()+"', '"+profile.getLastName()+"', '"+profile.getRating()+"')");
		if (check!=1) return false;
		for(int i=0;i<profile.getHistory().getNumOfHistories();i++) {
			if (!insertHistoryEntryToDB(profile.getUserID(),profile.getHistory().getEntry(i)))
				return false;
		}
		profile.resetDirtyBits();
		return true;
	}
	
	public boolean updateProfile(Profile profile) {
		//Update profile fields:
		if (profile.isDirty()) {
			int check = DBA.WriteToDB("UPDATE USERS SET FIRSTNAME='"+profile.getFirstName()+"',LASTNAME='"+profile.getLastName()+"', RATING='"+profile.getRating()+"'" +
					"WHERE USERID='"+profile.getUserID()+"'");			
			if (check!=1)
				return false;
		}
		//Update histories:
		History history = profile.getAddedHistories();
		for(int i=0;i<history.getNumOfHistories();i++) {
			if (!insertHistoryEntryToDB(profile.getUserID(),history.getEntry(i)))
				return false;
		}
		profile.resetDirtyBits();
		return true;
	}
	
	private boolean insertHistoryEntryToDB(long userID,historyEntry entry) {
		String opponents = new String(entry.getRivals().get(0));
		for (int i=1;i<entry.getRivals().size();i++) {
			opponents += DELIMITER;
			opponents +=entry.getRivals().get(i);
		}
		int check = DBA.WriteToDB("INSERT INTO GAME_HISTORY (USERID, OPPONENTS, RATING_CHANGE, GAME_DATE) " +
				 "VALUES ('"+userID+"', '"+opponents+"', '"+entry.getScore()+"', '"+entry.getDate()+"')");
		return (check==1);
	}

	public Profile queryProfile(long userID) throws SQLException {
		ResultSet rs = DBA.ReadFromDB("SELECT * FROM USERS WHERE USERID = \"" + userID +"\"");
		if (!rs.next()) 
			return null;
		Profile result = new Profile(userID,rs.getString("FIRSTNAME"),rs.getString("LASTNAME"),rs.getInt("RATING"));
		rs = DBA.ReadFromDB("SELECT * FROM GAME_HISTORY WHERE USERID = \"" + userID +"\"");
		while (rs.next()) {
			result.addHistoryEntry(rs.getDate("GAME_DATE"), parseRivals(rs.getString("OPPONENTS")), rs.getInt("RATING_CHANGE"));
		}
		result.resetDirtyBits();
		return result;
	}
	
	private List<String> parseRivals(String rivals) {
		String result[] = rivals.split(DELIMITER);
		return Arrays.asList(result);
	}
	
	public int deleteProfile(long userID) throws SQLException {
		int check = DBA.WriteToDB("DELETE FROM GAME_HISTORY WHERE USERID = \"" + userID +"\"");
		if (check == -1) return -1;
		check = DBA.WriteToDB("DELETE FROM USERS WHERE USERID = \"" + userID +"\"");
		return check;
	}
	
	public Map<Integer,List<Profile>> getHallOfFame() throws SQLException {
	    ReverseIntegerComparator comparator = new ReverseIntegerComparator();
	    Map<Integer, List<Profile>> hallOfFame = new TreeMap<Integer, List<Profile>>(comparator);
	    ResultSet rs = DBA.ReadFromDB("SELECT * FROM HALL_OF_FAME");
	    DBLogic innerDBL = new DBLogic();
	    innerDBL.InitDBConnection();
	    while (rs.next()) {
	        if (hallOfFame.containsKey(rs.getInt("SCORE"))) {
	            Profile profile = innerDBL.queryProfile(rs.getLong("USERID"));
	            hallOfFame.get(rs.getInt("SCORE")).add(profile);
	        } else {
	            List<Profile> profiles = new ArrayList<Profile>();
	            Profile profile = innerDBL.queryProfile(rs.getLong("USERID"));
	            profiles.add(profile);
	            hallOfFame.put(rs.getInt("SCORE"), profiles);
	        }
	    }
	    innerDBL.ShutDBConnection();
	    return hallOfFame;
	}
	
	public boolean updateHallOfFame(Map<Integer, List<Profile>> hallOfFame) {
       String hof_query = "CREATE TABLE HALL_OF_FAME (USERID BIGINT NOT NULL, " + 
       		"USERNAME VARCHAR(200) NOT NULL," +
       		"SCORE INT UNSIGNED NOT NULL)";
	    
	    DBA.WriteToDB("DROP TABLE HALL_OF_FAME");
	    int check = DBA.WriteToDB(hof_query);
	    if (check == -1) {
	        return false;
	    }
	    
	    for (int currentScore : hallOfFame.keySet()) {
	        for (Profile currentProfile : hallOfFame.get(currentScore)) {
	            Long userID = currentProfile.getUserID();
	            String username = currentProfile.getFullName();
	            check = DBA.WriteToDB("INSERT INTO HALL_OF_FAME (USERID, USERNAME, SCORE) " +
	                    "VALUES ('" + userID + "' , '" + username + "' , '"  + currentScore + "')");
	            if (check == -1) {
	                return false;
	            }
	        }
	    }
	    
	    return true;
	}
	
	
	public int deleteRoom(int roomID) {
	    int check = DBA.WriteToDB("DELETE FROM ROOMS WHERE ROOM_ID = \"" + roomID +"\"");
        if (check == -1) return -1;
        else return 1;
	}
	
	public boolean updateRoom(int roomID, String room_state) { 
	    int check = DBA.WriteToDB("UPDATE ROOMS SET ROOM_STATE='"+ room_state +"' " +
                "WHERE ROOM_ID='" + roomID + "'");          
        if (check!=1)
            return false;
        return true;
	}
	
	public int addNewRoom(String room_state) throws SQLException {
       int check = DBA.WriteToDB("INSERT INTO ROOMS (ROOM_STATE) " +
               "VALUES ('" + room_state + "')");
       if (check!=1) return 0;
       ResultSet rs = DBA.ReadFromDB("SELECT LAST_INSERT_ID()");
       rs.next();
       return rs.getInt(1);
	}
	
	public String queryRooms() throws SQLException {
       
	    
	    String rooms = "<rooms>";
	    
	    ResultSet rs = DBA.ReadFromDB("SELECT * FROM ROOMS" );
        while (rs.next()) {
            rooms += "<room>"+ 
                "<roomID>" + rs.getInt("ROOM_ID") + "</roomID>" +
                rs.getString("ROOM_STATE") + 
                "</room>";
        } 
        
        rooms += "</rooms>";
        
        return rooms;
	}
	
	public String queryRoomState(int roomID) throws SQLException {
	    ResultSet rs = DBA.ReadFromDB("SELECT ROOM_STATE FROM ROOMS WHERE ROOM_ID =\"" + roomID + "\"" );
	    if (rs.next()) {
	        return rs.getString("ROOM_STATE");
	    } else {
	        return null;
	    }
	}
	
	public int queryGameNumberOfPlayers(int roomID) throws SQLException {
        ResultSet rs = DBA.ReadFromDB("SELECT GAME_SIZE FROM GAMES WHERE ROOM_ID =\"" + roomID + "\"" );
        if (rs.next()) {
            return rs.getInt("GAME_SIZE");
        } else {
            return -1;
        }
	}
    
	public boolean updateGameNumberOfPlayers(int roomID, int numberOfPlayers) { 
        int check = DBA.WriteToDB("UPDATE GAMES SET GAME_SIZE='"+ numberOfPlayers + "' " +
                "WHERE ROOM_ID='" + roomID + "'");          
        if (check!=1) {
            return false;
        }
        return true;
    }
	
	public String queryGameData(int roomID) throws SQLException {
	    ResultSet rs = DBA.ReadFromDB("SELECT GAME_DATA FROM GAMES WHERE ROOM_ID =\"" + roomID + "\"");
	    if (rs.next()) {
            return rs.getString("GAME_DATA");
        } else {
            return null;
        }
	}
	
    public int addGameData(String game_data, int roomID) {
        int check = DBA.WriteToDB("INSERT INTO GAMES (GAME_DATA, ROOM_ID) "
                + "VALUES ('" + game_data + "', '" + roomID + "')");
        if (check != 1)
            return 0;
        return 1;
    }
    
    public boolean updateGameData(int roomID, String game_data, int actionCounter) { 
        int check = DBA.WriteToDB("UPDATE GAMES SET GAME_DATA='"+ game_data + "' " +
                "WHERE ROOM_ID='" + roomID + "'");          
        if (check!=1)
            return false;
        check = DBA.WriteToDB("UPDATE GAMES SET ACTION_COUNTER='"+ actionCounter + "' " +
                "WHERE ROOM_ID='" + roomID + "'");          
        if (check!=1)
            return false;
        return true;
    }
    
    public boolean postGameLog(int roomID, String game_log) { 
        int check = DBA.WriteToDB("UPDATE GAMES SET GAME_LOG='"+ game_log +"' " +
                "WHERE ROOM_ID='" + roomID + "'");          
        if (check!=1)
            return false;
        return true;
    }
    
    public boolean postPrivateGameLog(int roomID, String game_log) { 
        int check = DBA.WriteToDB("UPDATE GAMES SET GAME_PRIVATE_LOG='"+ game_log +"' " +
                "WHERE ROOM_ID='" + roomID + "'");          
        if (check!=1)
            return false;
        return true;
    }
    
	public String queryGameLog(int roomID) throws SQLException {
	    ResultSet rs = DBA.ReadFromDB("SELECT GAME_LOG FROM GAMES WHERE ROOM_ID =\"" + roomID + "\"");
	    if (rs.next()) {
            return rs.getString("GAME_LOG");
        } else {
            return null;
        }
	}
	
   public String queryPrivateGameLog(int roomID) throws SQLException {
        ResultSet rs = DBA.ReadFromDB("SELECT GAME_PRIVATE_LOG FROM GAMES WHERE ROOM_ID =\"" + roomID + "\"");
        if (rs.next()) {
            return rs.getString("GAME_PRIVATE_LOG");
        } else {
            return null;
        }
    }
   
   public int queryActionCounter(int roomID) throws SQLException {
	    ResultSet rs = DBA.ReadFromDB("SELECT ACTION_COUNTER FROM GAMES WHERE ROOM_ID =\"" + roomID + "\"");
	    if (rs.next()) {
	    	return rs.getInt("ACTION_COUNTER");
        } else {
            return -1;
        }
   }
   
   public boolean postChatMsg(int gameID,int playerIndex,String msg) {
	   if (!(playerIndex > 0 && playerIndex <= 6))
		   return false;
	   String slot = "PLAYER" + playerIndex;
	   msg = msg.replace("'", "''");
       int check = DBA.WriteToDB("UPDATE GAMES SET " + slot + "='"+ msg +"' " +
               "WHERE ROOM_ID='" + gameID + "'");          
       return (check==1);
   }
   
   public String queryChatMsg(int gameID,int playerIndex) throws SQLException {
	   if (!(playerIndex > 0 && playerIndex <= 6))
		   return null;
	   String slot = "PLAYER" + playerIndex;
       ResultSet rs = DBA.ReadFromDB("SELECT " + slot + " FROM GAMES WHERE ROOM_ID =\"" + gameID + "\"");
       if (rs.next()) {
           return rs.getString(slot);
       } else {
           return null;
       }
   }
}
